package cs340.stormtroopers.database;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;

import cs340.stormtroopers.hypeerweb.Node;
import cs340.stormtroopers.hypeerweb.SimplifiedNodeDomain;

/**
 * Creates a database and all necessary tables in order to be able to store and load
 * an arbitrary number of Nodes (and their respective attributes and connections)on disk.
 * Uses SQLite as the database to store the nodes. 
 * 
 * <pre>
 *  <b>Domain:</b>
 *		DATABASE_DIRECTORY			String
 *		DEFAULT_DATABASE_NAME		String
 *		singleton					HyPeerWebDatabase
 *		connection					Connection
 * 
 * <b>Instance Invariants:</b> 
 * 		DATABASE_DIRECTORY is a non-empty String representing the directory the database 
 * 			file will be stored in. 
 * 		DEFAULT_DATABASE_NAME is a non-empty String representing the name of the database file. 
 * 		Singleton is a private instance of the HyPeerWebDatabase. There may be one and only one 
 * 			instance of the HyPeerWebDatabase at any given time.
 * 		Connection represents a connection to the database file.   
 * 		
 * </pre>
 * 
 * @author Andrew Morrow
 * @author William Highland
 * 
 */
public class HyPeerWebDatabase
{
	public static String DATABASE_DIRECTORY = System.getProperty("user.home")
			+ "/CS340HyPeerWebData/";
	public static String DEFAULT_DATABASE_NAME = "data.sqlite";
	private static HyPeerWebDatabase singleton = null;

	private Connection connection;

	private HyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		doInitHyPeerWebDatabase(dbName);
	}

	/**
	 * Following the singleton pattern, this method returns the single HyPeerWebDatabase 
	 * object this class contains. 
	 * 
	 * @pre none
	 * 
	 * @post The returned HyPeerWebDatabase is guaranteed to be initialized and ready 
	 * to save or load information
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 * @throws IOException If the database cannot be accessed
	 * @throws ClassNotFoundException If the loaded class cannot be found
	 */
	public static HyPeerWebDatabase getSingleton() throws IOException,
			ClassNotFoundException, SQLException
	{
		if (singleton == null)
			singleton = new HyPeerWebDatabase(DEFAULT_DATABASE_NAME);
		return singleton;
	}

	/**
	 * Initializes the HyPeerWebDatabase with the given name
	 *
	 * @pre None
	 * @post The HyPeerWebDatabase will be initialized with all appropriate tables and relations 
	 * 
	 * @param dbName The name of the database to create or access
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 * @throws IOException If the database cannot be accessed
	 * @throws ClassNotFoundException If the loaded class cannot be found
	 */
	public static void initHyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		getSingleton().doInitHyPeerWebDatabase(dbName);
	}

	private void doInitHyPeerWebDatabase(String dbName) throws IOException,
			ClassNotFoundException, SQLException
	{
		if (connection != null)
			connection.close();
		// check that the database file exists
		// create it and its parent directories if they don't
		File databaseFile = new File(DATABASE_DIRECTORY + dbName);
		if (!databaseFile.exists())
		{
			databaseFile.getParentFile().mkdirs();
			databaseFile.createNewFile();
		}

		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection("jdbc:sqlite:"
				+ databaseFile.getAbsolutePath());
	}

	/**
	 * Initializes the HyPeerWebDatabase with the default database name
	 *
	 * @pre None
	 * @post The HyPeerWebDatabase will be initialized with all appropriate tables and relations 
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 * @throws IOException If the database cannot be accessed
	 * @throws ClassNotFoundException If the loaded class cannot be found
	 */
	public static void initHyPeerWebDatabase() throws IOException,
			ClassNotFoundException, SQLException
	{
		initHyPeerWebDatabase(DEFAULT_DATABASE_NAME);
	}

	/**
	 * Clears the HyPeerWebDatabase, dropping all associated tables. 
	 * 
	 * @pre None
	 * @post The HyPeerWebDatabase will be empty and all tables will have been dropped.
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 */
	public void clear() throws SQLException
	{
		dropTables();
		createTablesIfNeeded();
	}

	/**
	 * Closes the HyPeerWebDatabase.
	 * 
	 * @pre None
	 * @post The HyPeerWebDatabase will be closed and unavailable for loading or saving
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 */
	public void close() throws SQLException
	{
		connection.close();
	}

	///////////////////////////////////////////////////////////////// SAVING NODES

	
	/**
	 * Saves a Collection of Node objects into the sqlite database
	 * 
	 * @param nodes A Collection of Node objects to be saved
	 * 
	 * @pre The database must have been previously initialized.
	 * @post The database will now have all the information associated with each 
	 * node and their respective connections
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 * @throws IOException If the database cannot be accessed
	 * @throws ClassNotFoundException If the loaded class cannot be found
	 */
	public void saveNodes(Collection<Node> nodes) throws SQLException,
			IOException, ClassNotFoundException
	{
		saveNodeDelegates(NormalNodeDelegate.wrap(nodes));
	}

	/**
	 * Uses the delegate pattern to save nodes into the database. 
	 * 
	 * @param nodeIterator An Iterator of Delegate objects that will be saved into the database. 
	 * 
	 * @pre The database must have been previously initialized
	 * @post The database will now have all the information associated with each 
	 * node and their respective connections
	 * 
	 * @throws SQLException If there is a problem with the SQL Query
	 * @throws IOException If the database cannot be accessed
	 * @throws ClassNotFoundException If the loaded class cannot be found
	 */
	public void saveNodeDelegates(Iterator<Delegate> nodeIterator)
			throws SQLException, IOException, ClassNotFoundException
	{
		while (nodeIterator.hasNext())
		{
			Delegate d = nodeIterator.next();
			d.insertIntoDatabase(connection);
		}
	}

	// /////////////////////////////////////////////////////////////// GETTING
	// NODES

	/**
	 * Loads the information for each node from the database into a Collection 
	 * of CompleteNodeDomain objects.
	 *
	 * @pre The database must have been previously initialized.
	 * @post The Collection of SimplifiedNodeDomain that is returned will 
	 * contain all the attributes and connections of every Node in the database and no others.
	 *
	 * @throws SQLException If there is a problem with the SQL Query
	 */
	public ArrayList<SimplifiedNodeDomain> getSimplifiedNodeDomainSet()
			throws SQLException
	{
		ArrayList<SimplifiedNodeDomain> domains = new ArrayList<SimplifiedNodeDomain>();
		ResultSet nodes = connection.createStatement().executeQuery(
				"SELECT WebId, Height, FoldId, SurrogateFoldId, InverseSurrogateFoldId FROM Node;");
		while (nodes.next())
			domains.add(getNodeFromResultSet(nodes));
		nodes.close();
		return domains;
	}

	private SimplifiedNodeDomain getNodeFromResultSet(ResultSet resultSet)
			throws SQLException
	{
		int webId = resultSet.getInt("WebId");
		
		SimplifiedNodeDomain node = new SimplifiedNodeDomain(
				webId,
				resultSet.getInt("Height"),
				resultSet.getInt("FoldId"),
				resultSet.getInt("SurrogateFoldId"),
				resultSet.getInt("InverseSurrogateFoldId"),
				-1
		);

		node.setNeighbors(getNeighbors(webId));

		node.setDownPointers(getSurrogateNeighbors(webId));
		node.setUpPointers(getInverseSurrogateNeighbors(webId));

		return node;
	}
	
	private HashSet<Integer> getNeighbors(int webId) throws SQLException
	{
		PreparedStatement getNeighbors = connection
				.prepareStatement("SELECT Right FROM Neighbor WHERE Left = ? ORDER BY Right;");
		getNeighbors.setInt(1, webId);
		ResultSet neighbors = getNeighbors.executeQuery();
		HashSet<Integer> neighborIds = new HashSet<Integer>();

		while (neighbors.next())
			neighborIds.add(neighbors.getInt("Right"));

		getNeighbors.close();
		return neighborIds;
	}

	private HashSet<Integer> getSurrogateNeighbors(int webId)
			throws SQLException
	{
		PreparedStatement getSurrogateNeighbors = connection
				.prepareStatement("SELECT Bottom FROM SurrogateNeighbor WHERE Top = ?;");
		getSurrogateNeighbors.setInt(1, webId);
		ResultSet surrogateNeighbors = getSurrogateNeighbors.executeQuery();
		HashSet<Integer> surrogateNeighborIds = new HashSet<Integer>();

		while (surrogateNeighbors.next())
			surrogateNeighborIds.add(surrogateNeighbors.getInt("Bottom"));

		getSurrogateNeighbors.close();
		return surrogateNeighborIds;
	}

	private HashSet<Integer> getInverseSurrogateNeighbors(int webId)
			throws SQLException
	{
		PreparedStatement getInverseSurrogateNeighbors = connection
				.prepareStatement("SELECT Top FROM SurrogateNeighbor WHERE Bottom = ?;");
		getInverseSurrogateNeighbors.setInt(1, webId);
		ResultSet inverseSurrogateNeighbors = getInverseSurrogateNeighbors
				.executeQuery();
		HashSet<Integer> inverseSurrogateNeighborIds = new HashSet<Integer>();

		while (inverseSurrogateNeighbors.next())
			inverseSurrogateNeighborIds.add(inverseSurrogateNeighbors.getInt("Top"));

		getInverseSurrogateNeighbors.close();
		return inverseSurrogateNeighborIds;
	}

	// /////////////////////////////////////////////////////////////// CREATING
	// TABLES

	private void createTablesIfNeeded() throws SQLException
	{
		Statement createTablesStatement = connection.createStatement();
		createTablesStatement.addBatch(
				"CREATE TABLE IF NOT EXISTS Node (" +
				"WebId integer primary key," +
				"Height integer not null," +
				"FoldId int," +
				"SurrogateFoldId integer," +
				"InverseSurrogateFoldId integer);"
		);
		createTablesStatement.addBatch(
				"CREATE TABLE IF NOT EXISTS SurrogateNeighbor (" +
				"Id integer primary key," +
				"Top integer not null," +
				"Bottom integer not null);"
		);
		createTablesStatement.addBatch(
				"CREATE TABLE IF NOT EXISTS Neighbor (" +
				"Id integer primary key," +
				"Left integer not null," +
				"Right integer not null);"
		);
		createTablesStatement.executeBatch();
		createTablesStatement.close();

		Statement createIndexStatement = connection.createStatement();
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS SurrogateNeighborTopIndex ON SurrogateNeighbor(Top);");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS SurrogateNeighborBottomIndex ON SurrogateNeighbor(Bottom);");
		createIndexStatement
				.addBatch("CREATE INDEX IF NOT EXISTS NeighborIndex ON Neighbor(Left);");

		createIndexStatement.executeBatch();
		createIndexStatement.close();
	}

	// /////////////////////////////////////////////////////////////// DROPPING
	// TABLES

	private void dropTables() throws SQLException
	{
		Statement dropIndexesStatement = connection.createStatement();
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS SurrogateNeighborTopIndex;");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS SurrogateNeighborBottomIndex;");
		dropIndexesStatement
				.addBatch("DROP INDEX IF EXISTS NeighborIndex;");
		dropIndexesStatement.executeBatch();
		dropIndexesStatement.close();

		Statement dropTablesStatement = connection.createStatement();
		dropTablesStatement.addBatch("DROP TABLE IF EXISTS Node;");
		dropTablesStatement.addBatch("DROP TABLE IF EXISTS SurrogateNeighbor;");
		dropTablesStatement
				.addBatch("DROP TABLE IF EXISTS Neighbor;");
		dropTablesStatement.executeBatch();
		dropTablesStatement.close();
	}
}
